package com.oocl.venus.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.oocl.venus.dao.DepartmentDao;
import com.oocl.venus.entity.Department;
import com.oocl.venus.entity.Person;
import com.oocl.venus.utils.DbUtils;


public class DepartmentDaoImpl implements DepartmentDao {

	public int addDepartment(Department d) {
		String sql = "insert into department(did,dname,city) values(dep.nextval,?,?)";
		Connection con = null;
		PreparedStatement pst = null;
		con = DbUtils.connect();
		int m = 0;
		try {
			pst = con.prepareStatement(sql);
			con.setAutoCommit(false);
			pst.setString(1, d.getDname());
			pst.setString(2, d.getCity());

			m = pst.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			DbUtils.free(con, pst, null);
		}

		return m;
	}

	public int deleteDepartment(int did) {
		String sql = "delete from department where did=?";
		Connection con = null;
		PreparedStatement pst = null;
		con = DbUtils.connect();
		int m = 0;
		try {
			pst = con.prepareStatement(sql);
			con.setAutoCommit(false);
			pst.setInt(1, did);

			m = pst.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			DbUtils.free(con, pst, null);
		}

		return m;
	}

	public int updateDepartment(Department d) {
		String sql = "update department set dname=?,city=? where did=?";
		Connection con = null;
		PreparedStatement pst = null;
		con = DbUtils.connect();
		int m = 0;
		try {
			pst = con.prepareStatement(sql);
			con.setAutoCommit(false);

			pst.setString(1, d.getDname());
			pst.setString(2, d.getCity());
			pst.setInt(3, d.getDid());

			m = pst.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			DbUtils.free(con, pst, null);
		}

		return m;
	}

	public List<Department> showAllDepartment() {
		List<Department> cs = new ArrayList<Department>();

		String sql = "select * from department";
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		con = DbUtils.connect();
		try {
			pst = con.prepareStatement(sql);
			rs = pst.executeQuery();
			while (rs.next()) {
				int did = rs.getInt("did");
				String dname = rs.getString("dname");
				String city = rs.getString("city");

				Department d = new Department(dname, city);
				d.setDid(did);
				cs.add(d);
			}
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtils.free(con, pst, null);
		}

		return cs;
	}

	public List<Person> showDepartmentAll(int did) {
		List<Person> cs = new ArrayList<Person>();
		String sql = "select * from person where did=?";
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		con = DbUtils.connect();
		try {
			pst = con.prepareStatement(sql);
			pst.setInt(1, did);
			rs = pst.executeQuery();
			while (rs.next()) {
				String pname = rs.getString("pname");
				String birthday = rs.getString("birthday");
				String tel = rs.getString("tel");
				int pid = rs.getInt("pid");
				int salary = rs.getInt("salary");

				Person p = new Person(pname, birthday, tel, did, salary);
				p.setPid(pid);
				cs.add(p);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtils.free(con, pst, null);
		}

		return cs;
	}

	public Department loadDepartment(int did) {
		Department d = null;
		String sql = "select * from department where did=?";
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		con = DbUtils.connect();
		try {
			pst = con.prepareStatement(sql);
			pst.setInt(1, did);
			rs = pst.executeQuery();
			if (rs.next()) {
				String dname = rs.getString("dname");
				String city = rs.getString("city");

				d = new Department(dname, city);
				d.setDid(did);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtils.free(con, pst, null);
		}

		return d;
	}

}
